package zorg.Project;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

public class Database {
	private static final String DATABASE_NAME = "ZorgsDBs.db";
	   private static final int DATABASE_VERSION = 1;

	   private Context context;
	   private SQLiteDatabase db;

	   private SQLiteStatement insertWinkelStmt;
	   private SQLiteStatement insertBeoordeelStmt;
	   private SQLiteStatement insertReactieStmt;
	   
	   public Database(Context context) {
	      this.context = context;
	      OpenHelper openHelper = new OpenHelper(this.context);
	      this.db = openHelper.getWritableDatabase();
	      this.insertWinkelStmt = this.db.compileStatement("INSERT INTO Winkel VALUES (? ,? ,? ,? ,? ,? ,? )");
	      this.insertBeoordeelStmt = this.db.compileStatement("INSERT INTO Beoordeling VALUES (?, ? ,? ,? ,?, ? )");
	      this.insertReactieStmt = this.db.compileStatement("INSERT INTO Reacties VALUES (?, ? ,? ,? ,?, ? )");
	   }

	   
	   
	   public long insertWinkel(int id,String Naam,String Adres,String Postcode,String Telefoon,double Lat,double Long) {
		  this.insertWinkelStmt.bindLong(1, id); 
		  this.insertWinkelStmt.bindString(2, Naam);
		  this.insertWinkelStmt.bindString(3, Adres);
		  this.insertWinkelStmt.bindString(4, Postcode);
		  this.insertWinkelStmt.bindString(5, Telefoon);
		  this.insertWinkelStmt.bindDouble(6, Lat);
		  this.insertWinkelStmt.bindDouble(7, Long);
	      return this.insertWinkelStmt.executeInsert();
	   }
	   
	   public long insertBeoordeling(int id,int WinkelID,int TelefoonID,int Toilet,int Toegankelijkheid,int padBreedte) {
		  this.insertBeoordeelStmt.bindLong(1, id); 
		  this.insertBeoordeelStmt.bindLong(2, WinkelID); 
		  this.insertBeoordeelStmt.bindLong(3, TelefoonID); 
		  this.insertBeoordeelStmt.bindLong(4, Toilet); 
		  this.insertBeoordeelStmt.bindLong(5, Toegankelijkheid); 
		  this.insertBeoordeelStmt.bindLong(6, padBreedte); 
	      return this.insertBeoordeelStmt.executeInsert();
	   }

	   public long insertReactie(int id,int WinkelID,int TelefoonID,String Datum,String Gebruiker,String Reactie) {
			  this.insertReactieStmt.bindLong(1, id); 
			  this.insertReactieStmt.bindLong(2, WinkelID); 
			  this.insertReactieStmt.bindLong(3, TelefoonID); 
			  this.insertReactieStmt.bindString(4, Gebruiker); 
			  this.insertReactieStmt.bindString(5, Reactie); 
			  this.insertReactieStmt.bindString(6, Datum); 
		      return this.insertReactieStmt.executeInsert();
		   }
	   
	   public void deleteAll() {
	      this.db.delete("Winkel", null, null);
	      this.db.delete("Beoordeling", null, null);
	      this.db.delete("Reacties", null, null);
	   }

	   public List<String[]> selectAll() {
	      List<String[]> list = new ArrayList<String[]>();
	      Cursor cursor = this.db.query("Winkel", new String[] { "id","Naam","Adres","Postcode","Telefoon","Latitude","Longitude" }, 
	        null, null, null, null, "ID asc");
	      if (cursor.moveToFirst()) {
	         do {
	        	 String[] tmp = new String[11];
	        	 tmp[0]=cursor.getString(0);
	        	 tmp[1]=cursor.getString(1);
	        	 tmp[2]=cursor.getString(2);
	        	 tmp[3]=cursor.getString(3);
	        	 tmp[4]=cursor.getString(4);
	        	 tmp[5]=cursor.getString(5);
	        	 tmp[6]=cursor.getString(6);
   	      		 Cursor cursor2 = this.db.query("Beoordeling", new String[] { "ID","WinkelID","TelefoonID","Toilet","Toegankelijkheid","PadBreedte" }, 
	  	  	        "WinkelID = " + tmp[0], null, null, null, "ID asc");
      		 	if (cursor2.moveToFirst()) {
      		 		int toilet=0;
      		 		double padbreedte=0;
      		 		double toegankelijkheid=0;
      		 		double rows=0;
      		 		do {
      		 			rows++;
      		 			if (cursor2.getString(3).equals("0"))
      		 				toilet--;
      		 			else if (cursor2.getString(3).equals("1"))
      		 				toilet++;
      		 			int tmpbreedte=0;
      		 			int tmptoegank=0;
      		 			try {
      		 				tmptoegank=Integer.parseInt(cursor2.getString(4));
						} catch (Exception e) {
						}
      		 			try {
      		 				tmpbreedte=Integer.parseInt(cursor2.getString(5));
						} catch (Exception e) {
						}
						padbreedte+=tmpbreedte;
						toegankelijkheid+=tmptoegank;
      		 		}while (cursor2.moveToNext());
      		 		if (cursor2 != null && !cursor2.isClosed()) {
	     		         cursor2.close();
      		 		}
      		 		if (toilet>0)
      		 			tmp[7]="1";
      		 		else
      		 			tmp[7]="0";
      		 		if (rows>0)
      		 		{
      		 			tmp[8]=String.valueOf(Math.round(toegankelijkheid/rows));
      		 			tmp[9]=String.valueOf(Math.round(padbreedte/rows));
      		 			tmp[10]=String.valueOf(Math.round(((padbreedte/rows)+(toegankelijkheid/rows))/2));
      		 		}
      		 		else
      		 		{
      		 			tmp[8]="0";
      		 			tmp[9]="0";
      		 			tmp[10]="0";
      		 		}
      		 	}
   	      		 	
	            list.add(tmp); 
	         } while (cursor.moveToNext());
	      }
	      if (cursor != null && !cursor.isClosed()) {
	         cursor.close();
	      }
	      return list;
	   }

	   public List<String[]> GetReacties(String WinkelID) {
		   List<String[]> list = new ArrayList<String[]>();
		      Cursor cursor = this.db.query("Reacties", new String[] { "ID,WinkelID,TelefoonID","Gebruiker","Reactie","Datum" }, 
	    		  "WinkelID = " + WinkelID, null, null, null, "Datum asc");
		      if (cursor.moveToFirst()) {
		         do {
		        	 String[] tmp = new String[4];
		        	 tmp[0]=cursor.getString(3);
		        	 tmp[1]=cursor.getString(5);
		        	 tmp[3]=cursor.getString(4);
	   	      		 Cursor cursor2 = this.db.query("Beoordeling", new String[] { "ID","WinkelID","TelefoonID","Toilet","Toegankelijkheid","PadBreedte" }, 
		  	  	        "WinkelID = " + WinkelID + " AND TelefoonID = " + cursor.getString(2), null, null, null, "ID asc");
	      		 	if (cursor2.moveToFirst()) {
	      		 		double padbreedte=0;
	      		 		double toegankelijkheid=0;
	      		 		double rows=0;
	      		 		do {
	      		 			rows++;
	      		 			int tmpbreedte=0;
	      		 			int tmptoegank=0;
	      		 			try {
	      		 				tmptoegank=Integer.parseInt(cursor2.getString(4));
							} catch (Exception e) {
							}
	      		 			try {
	      		 				tmpbreedte=Integer.parseInt(cursor2.getString(5));
							} catch (Exception e) {
							}
							padbreedte+=tmpbreedte;
							toegankelijkheid+=tmptoegank;
	      		 		}while (cursor2.moveToNext());
		      		      if (cursor2 != null && !cursor2.isClosed()) {
		     		         cursor2.close();
		     		      }
	      		 		if (rows>0)
	      		 		{
	      		 			tmp[2]=String.valueOf(Math.round(((padbreedte/rows)+(toegankelijkheid/rows))/2));
	      		 		}
	      		 		else
	      		 		{
	      		 			tmp[2]="0";
	      		 		}
	      		 	}
		            list.add(tmp); 
		         } while (cursor.moveToNext());
		      }
		      if (cursor != null && !cursor.isClosed()) {
		         cursor.close();
		      }
		      return list;
	   }
	   private static class OpenHelper extends SQLiteOpenHelper {

	      OpenHelper(Context context) {
	         super(context, DATABASE_NAME, null, DATABASE_VERSION);
	      }

	      @Override
	      public void onCreate(SQLiteDatabase db) {
	    	  db.execSQL("CREATE TABLE Winkel (ID INTEGER, Naam TEXT,Adres TEXT, Postcode TEXT,Telefoon TEXT, Latitude DOUBLE, Longitude DOUBLE)");
	    	  db.execSQL("CREATE TABLE Beoordeling (ID INTEGER, WinkelID INTEGER, TelefoonID INTEGER, Toilet INTEGER, Toegankelijkheid INTEGER,PadBreedte INTEGER)");
	    	  db.execSQL("CREATE TABLE Reacties (ID INTEGER, WinkelID INTEGER, TelefoonID INTEGER, Gebruiker TEXT, Reactie TEXT,Datum TEXT)");
	      }

	      @Override
	      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	         Log.w("Example", "Upgrading database, this will drop tables and recreate.");
	         db.execSQL("DROP TABLE IF EXISTS Winkel");
	         db.execSQL("DROP TABLE IF EXISTS Beoordeling");
	         db.execSQL("DROP TABLE IF EXISTS Reacties");
	         onCreate(db);
	      }
	   }
}
